#!/bin/bash
# -*- encoding: utf8 -*-
from BModel.md5Hive import md5Encode
from config.config import Hivefile, HiveHistoryfile, count
from config.config import sqllimit


"""old库存储的数据 库时间节点（2017-09）"""
def OldDataInsertHiveNewThree(db):
    print 'sql查询'
    oldsql = "SELECT ReviewID,OrderID FROM `bw_ctrip_review_cn` WHERE WritingDate >='2015-01-01 00:00:00' AND WritingDate < '2016-01-01 00:00:00'"
    # fp = open(HiveHistoryfile, 'a+')
    # fp.write("查询区间2013-01-01 00:00:00"+ "\n")
    all = db.bmquery(oldsql, 'ctripo')
    reviewlist = []
    revirenum = len(reviewlist)

    fp = open(HiveHistoryfile, 'a+')
    # fp.write("查出旧表旧数据全部" + str(revirenum) + "条" + "\n")
    # print all['data'][0]['ReviewID']
    if len(all['data']) > 0:
        fp.write("查出旧表旧数据全部" + str(len(all['data'])) + "条" + "\n")
        page = int(len(all['data']) / sqllimit) + 1
        i = 0
        for i in range(0, page):
            print i
            start = i * sqllimit
            end = sqllimit * (i + 1)
            import time
            time = time.strftime("%Y-%m-%d")
            insertsql, stip = "", ""
            for v in all['data'][start:end]:
                insertsql = insertsql + stip + "(%s,'%s','%s')" % (v['ReviewID'], md5Encode(str(v['OrderID'])), time)
                stip = ","
            # print insertsql
            if insertsql:
                fp.write(insertsql + "\n")
                hotelsql = "INSERT INTO BW_Order_Review_List(`ReviewID`,`Order`,`DateTime`) VALUES %s " % (insertsql)
                db.bmquery(hotelsql, 'hive')
                print 'ok'


"""New库存储的数据 库时间节点（2017-09）"""
def NewDataInsertHive(db):
    newsql = "SELECT ID FROM `bw_ctrip_review_cn_v2` WHERE WritingDate >= %s and Rating <= 4 "%('1527782400000')
    all = db.bmquery(newsql,'ctripn')
    reviewlist = []
    if True == all['status']:
        for v in all['data']:
            reviewlist.append(v['ID'])
    else:
        return False
    maxid = 0
    revirenum = len(reviewlist)
    fp = open(HiveHistoryfile, 'a+')
    fp.write("查出新表新数据全部" + str(revirenum) + "条" + "\n")
    if 0 < revirenum:
        page = int(revirenum/sqllimit)+1
        i = 0
        for i in range(0,page):
            start = i*sqllimit
            end = sqllimit*(i+1)
            tmplist = reviewlist[start:end]
            insertsql,stip = "",""
            sqlreviewlist,sqlstip = "",""
            for v in tmplist:
                sqlreviewlist = str(sqlreviewlist) +sqlstip + str(v)
                sqlstip = ","
            if sqlreviewlist:
                fp.write("New第" + str(i) + "次10W数据" + "\n")
                oldsql = "SELECT ID,ReviewID,Rating,OrderID FROM `bw_ctrip_review_cn_v2` WHERE ID IN (%s)"%(sqlreviewlist)
                reviewdata = db.bmquery(oldsql,'ctripn')
                if True == reviewdata['status']:
                    for v in reviewdata['data']:
                        maxid = v['ID']
                        import time
                        time = time.strftime("%Y-%m-%d")
                        insertsql = insertsql+stip +"(%s,'%s','%s')"%(v['ReviewID'], md5Encode(str(v['OrderID'])), time)
                        stip = ","
            if insertsql:
                hotelsql = "INSERT INTO BW_Order_Review_List(`ReviewID`,`Order`,`DateTime`) VALUES%s"%(insertsql)
                db.bmquery(hotelsql,'hive')
                fp.write("塞进表新表旧数据" + str(len(insertsql)) + "条" + "\n")

    if 0 < maxid:
        fp = open(Hivefile,'w+')
        fp.write(str(maxid))
        fp.close()

